create external table jms_tmp.dm_network_outbreak_dt
(
virt_code     string COMMENT '虚拟代理区code' ,
virt_name   string COMMENT '虚拟代理区' ,
agent_code  string COMMENT '代理区code' ,
agent_name  string COMMENT '代理区名字' ,
code      string COMMENT '网点code' ,
name      string COMMENT '网点' ,
day_avg    int COMMENT '日均到件量' ,
scan_sum   int COMMENT '当日签收量' ,
cnt       int COMMENT '当日派件员数量' ,
retention_count_7d   int COMMENT '七天内滞留量' ,
 digestion_ability   decimal(16,4) COMMENT '消化能力' ,
retention_count_1d    int COMMENT '一天内滞留量' ,
retention_1d_rate     decimal(16,4) COMMENT '一天滞留率' ,
2_1_sum            int COMMENT '第二天滞留量' ,
retention_second_day_rate    decimal(16,4) COMMENT '第二天滞留量占七天滞留总量的比例' ,
7_2_sum                  int COMMENT '37天滞留量' ,
retention_3_7_day_rate decimal(16,4) COMMENT '第3天到第7天滞留量占七天滞留总量的比例' )
comment '网点防爆舱明细临时表'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_tmp.db/external/dm_network_outbreak_dt'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
    );


create external table jms_dm.dm_network_outbreak_detail_dt
(
virt_code   string COMMENT '虚拟代理区code' ,
virt_name    string COMMENT '虚拟代理区' ,
agent_code   string COMMENT '代理区code' ,
agent_name    string COMMENT '代理区' ,
code     string COMMENT '网点code' ,
name     string COMMENT '网点' ,
day_arrive_sum   int COMMENT '当日到达量' ,
t_day_arrive_sum int COMMENT '昨日到大量' ,
arrive_rate   decimal(16,4) COMMENT '到件环比' ,
day_sign_sum    int COMMENT '当日签收量' ,
t_day_sign_sum int COMMENT '昨日签收量' ,
sign_rate       decimal(16,4) COMMENT '签收比' ,
day_deliver_sum   int COMMENT '今日派件员数量' ,
t_day_deliver_sum int COMMENT '昨日派件员数量' ,
deliver_rate   decimal(16,4) COMMENT '派件员环比' ,
retention_count_7d    int COMMENT '七天滞留量' ,
digestion_ability     decimal(16,4) COMMENT '消化能力' ,
retention_count_1d    int COMMENT '当日滞留量' ,
retention_1d_rate     decimal(16,4) COMMENT '当日滞留率' ,
2_1_sum    int COMMENT '第二日滞留量' ,
retention_second_day_rate   decimal(16,4) COMMENT '第二日滞留量占比' ,
7_2_sum    int COMMENT 't_2到T7滞留量' ,
retention_3_7_day_rate   decimal(16,4) COMMENT 't_2到T7滞留量占比',
date_time string  comment'业务日期,
end_provider_id string comment'省份id',
end_provider_name string comment '省份'
)
comment '网点爆仓明细正式表'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_network_outbreak_detail_dt'
TBLPROPERTIES (
'discover.partitions' = 'false',
'parquet.column.index.access' = 'true'
    );
alter table jms_dm.dm_network_outbreak_detail_dt add columns (
end_provider_id string comment'省份id',
end_provider_name string comment '省份') cascade ;




create external table jms_dm.dm_network_retention_warming_detail_new_dt
(
    virt_code                 string COMMENT '虚拟代理区code',
    virt_name                 string COMMENT '虚拟代理区',
    agent_code                string COMMENT '代理区code',
    agent_name                string COMMENT '代理区',
    end_provider_id           string COMMENT '省份id',
    end_provider_name          string COMMENT '省份',
    code                      string COMMENT '网点code',
    name                      string COMMENT '网点',
    day_arrive_sum            int COMMENT '当日到达量',
    t_day_arrive_sum          int COMMENT '昨日到大量',
    arrive_rate               decimal(16, 4) COMMENT '到件环比',
    day_sign_sum              int COMMENT '当日签收量',
    t_day_sign_sum            int COMMENT '昨日签收量',
    sign_rate                 decimal(16, 4) COMMENT '签收比',
    day_deliver_sum           int COMMENT '今日派件员数量',
    t_day_deliver_sum         int COMMENT '昨日派件员数量',
    deliver_rate              decimal(16, 4) COMMENT '派件员环比',
            -- 以下为新增
           retention_count_3d int comment '三天滞留总量'           --
         , new_digestion_ability decimal(16, 4) comment '新的压力值'        --
         , tention_count_1d int comment '当日滞留量'  --
         , retention_1d_new_rate decimal(16, 4) comment ' T-1新的滞留率'        --
         , 2_1_sum int comment '第二日滞留量'  --
         , retention_second_day_new_rate decimal(16, 4) comment '第二天新的滞留率'--
         , t_3_retention_sum int comment 'T-3滞留量'            --
         , retention_3_2_day_rate decimal(16, 4) comment 'T-3的滞留率'       --
         , date_time date comment '业务日期'
) comment '网点滞留预警明细正式表new20230306'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_network_retention_warming_detail_new_dt'
TBLPROPERTIES (
' discover.partitions' = 'false',
' parquet.column.index.access ' = 'true'
);